%macro clean2(year);    /* macro used to clean 2019 */

FILENAME IN "&year"; 

PROC CIMPORT DATA = y&year INFILE = IN;
RUN;

data y&year;
	set y&year (keep = X42001 X411 X413 x432 X7132 X8022 X414 X5729 X6772 X6773 X701 X101 X411 X1104 X1115 X1126 X1103 X1114 X1125);

	weight = x42001;   /* Revised Kennickell-Woodburn consistent weight: accounts for systemative deviations from CPS estimates of homeownership by racial/ethnic groups. This weight should be used for all estimations using the final 1992 SCF data for which weights are appropriate. */	

	access = 0;
            if ( x411 > 0 ) then    /*How many different (TYPE) credit card accounts such BANK-TYPE: Visa, Mastercard, Discover, Optima do you (and your family living here) have? */
                do;
                access=1;
                end;

	year = &year;

    debt = 0;
            if ( x413 > 0 ) then    /* After the last payment(s) (was/were) made, what was the total balance still owed on (this account/all these accounts)? */
                do;
                debt = x413;
                end;

	pattern=1;
		if ( x432 = 1 ) then
			do;
			pattern=0;
			end;

	interest = -1;		
		if ( X7132 >= 0 ) then
			do;
			interest = X7132/100;  /* What interest rate do you pay on the card where you have the largest balance? */
			end;

	age = X8022;   /* FOR THE RESPONDENT, THIS VARIABLE CONTAINS THE DATE-OF-BIRTH AGE UNLESS HE/SHE REJECTS THAT AGE WHEN ASKED FOR CONFIRMATION AND PROVIDES ANOTHER AGE (SEE X14 BELOW). */

    limit = -1;
         if ( x414 > 0 ) then    /* What is the maximum amount you could borrow on (this/all of these) bank-type account(s); that is, what is your total credit limit? */
            do;
            limit = X414;
            end;

	income = x5729;

	bankruptcy = 0;
		if ( x6772 = 1 & x6773 < 2 ) then	
/*		if ( x6772 = 1 ) then */
			do;
			bankruptcy=1;
			end;

	renter = 0;
			if ( X701 = 2 ) then
			do;
			renter = 1;
			end;

	nhl = X101;
		if ( nhl > 2 ) then
		do;
		nhl=2;
		end;

	ncc = X411;

	heloc_limit1 = 0;
		if ( X1103 = 1 & X1104 > 0 ) then
		do;
		heloc_limit1=X1104;
		end;

	heloc_limit2 = 0;
		if ( X1114 = 1 & X1115 > 0 ) then
		do;
		heloc_limit2=X1115;
		end;

	heloc_limit3 = 0;
		if ( X1125 = 1 & X1126 > 0 ) then
		do;
		heloc_limit3=X1126;
		end;

	heloc_limit = heloc_limit1 + heloc_limit2 + heloc_limit3;

	other_limit1 = 0;
		if ( X1103 = 5 & X1104 > 0 ) then
		do;
		other_limit1=X1104;
		end;

	other_limit2 = 0;
		if ( X1114 = 5 & X1115 > 0 ) then
		do;
		other_limit2=X1115;
		end;

	other_limit3 = 0;
		if ( X1125 = 5 & X1126 > 0 ) then
		do;
		other_limit3=X1126;
		end;

	other_limit = other_limit1 + other_limit2 + other_limit3;

	run;

data y&year;
    set y&year (keep = weight access year debt pattern interest age limit income bankruptcy renter nhl ncc heloc_limit other_limit);
    run;

%mend;

/* clean and pull variables */
%clean2(2019); 

/* organized contains data for all years  changes: debtor_interest_assessed*/
data organized;
	set y2019;

    /* delete if age is not available */ 
    if ( missing(age) ) then delete; 

    /* delete if income is not available */
	if ( missing(income) ) then delete;

    /* delete if income is negative */
	if ( income < 0 ) then delete; 

	if ( limit < 0 & debt > 0 ) then delete; 

	if ( limit < 0 ) then	
		do;
		limit=0;
		end;

	/* finding debtors with or without interest */
	debtor_all=0;
        if ( debt > 0 ) then
           do;
		   debtor_all=1;
           end;

	/* finding debtors paying interest */
	ind=0;
        if ( debt > 0 & pattern = 1 ) then
           do;
           ind=1;
           end;
	
	/* utilization */
	utilization_above_10=0;
		if ( debt/limit > .1 ) then
			do;
			utilization_above_10=1;
			end;

	utilization_above_25=0;
		if ( debt/limit > .25 ) then
			do;
			utilization_above_25=1;
			end;

	utilization_above_50=0;
		if ( debt/limit > .5 ) then
			do;
			utilization_above_50=1;
			end;

	utilization_above_75=0;
		if ( debt/limit > .75 ) then
			do;
			utilization_above_75=1;
			end;

	utilization_above_95=0;
		if ( debt/limit > .95 ) then
			do;
			utilization_above_95=1;
			end;

	utilization_above_99=0;
		if ( debt/limit > .99 ) then
			do;
			utilization_above_99=1;
			end;

	utilization_above_100=0;
		if ( debt/limit >= 1 ) then
			do;
			utilization_above_100=1;
			end;

	utilization=0;
		if ( limit > 0 ) then
			do;
			utilization = debt/limit;
			end;

	/* compute interest assessed debt */
	debt_interest_assessed=0;
		if ( ind = 1 ) then					
			do;								
			debt_interest_assessed=debt;	
			end;				

	/* individual_debt_y */
	ind_debt_y=0;
		do;
		ind_debt_y=debt/income;
		end;

	/* individual_debt_interest_assessed_y */
	ind_debt_interest_assessed_y=0;
		do;
		ind_debt_interest_assessed_y=debt_interest_assessed/income;
		end;

	/* compute log income */
	log_income = log(income);

	/* set 5 year age */
	age5=age;
		if ( age < 20 ) then
			do;
			age5=17;
			end;
		if ( age > 19 & age < 25 ) then
			do;
			age5=22;
			end;
		if ( age > 24 & age < 30 ) then
			do;
			age5=27;
			end;
		if ( age > 29 & age < 35 ) then
			do;
			age5=32;
			end;
		if ( age > 34 & age < 40 ) then
			do;
			age5=37;
			end;
		if ( age > 39 & age < 45 ) then
			do;
			age5=42;
			end;
		if ( age > 44 & age < 50 ) then
			do;
			age5=47;
			end;
		if ( age > 49 & age < 55 ) then
			do;
			age5=52;
			end;
		if ( age > 54 & age < 60 ) then
			do;
			age5=57;
			end;
		if ( age > 59 & age < 65 ) then
			do;
			age5=62;
			end;
		if ( age > 64 & age < 70 ) then
			do;
			age5=67;
			end;
		if ( age > 69 ) then
			do;
			age5=67;
			end;
		if ( age5 = 17 ) then delete;
		if ( age5 = 22 ) then delete;

	ind_limit_y=0;
		do;
		ind_limit_y=limit/income;
		end;

	limit_debtor=0;
		if ( debt > 0 ) then
			do;
			limit_debtor = limit;
			end;

	access_heloc=0;
		if ( heloc_limit > 0 ) then
			do;
			access_heloc=1;
			end;

	access_other=0;
		if ( other_limit > 0 ) then
			do;
			access_other=1;
			end;

	run;

proc export data = organized
	outfile="output_organized.csv"
	dbms=csv replace;
run;

/* all HH */
proc means data = organized noprint; 
	freq weight;	
	var access ind debtor_all debt_interest_assessed debt income bankruptcy utilization_above_10 utilization_above_25 utilization_above_50 utilization_above_75 utilization_above_95 utilization_above_99 utilization_above_100 limit limit_debtor utilization heloc_limit other_limit access_heloc access_other;
	output out = output_all_hh (drop = _:) mean(access ind debtor_all debt_interest_assessed debt income bankruptcy utilization_above_10 utilization_above_25 utilization_above_50 utilization_above_75 utilization_above_95 utilization_above_99 utilization_above_100 limit limit_debtor utilization heloc_limit other_limit access_heloc access_other) = access interest_assessed_debtors all_debtors debt_interest_assessed debt income bankruptcy utilization_above_10 utilization_above_25 utilization_above_50 utilization_above_75 utilization_above_95 utilization_above_99 utilization_above_100 limit limit_debtor utilization heloc_limit other_limit access_heloc access_other;
	run;

proc export data = output_all_hh
	outfile="output_all_hh.csv"
	dbms=csv replace;
run;

/* by income */
data dummy;
	set organized;
	if ( age < 20 ) then delete;	
	run;

proc univariate data = dummy noprint;
	freq weight;
	by year;
	var income;
	output out = temp pctlpre = P_ pctlpts = 20, 40, 60, 80;
	run;

data dummy;
	merge dummy temp;
	by year; 
	run;

data dummy;
	set dummy;

	income_group=0;

		if ( income < P_20 ) then
			do;
			income_group = 1;
			end;

		if ( income >= P_20 && income < P_40 ) then
			do;
			income_group = 2;
			end;

		if ( income >= P_40 && income < P_60 ) then
			do;
			income_group = 3;
			end;

		if ( income >= P_60 && income < P_80 ) then
			do;
			income_group = 4;
			end;

		if ( income >= P_80 ) then
			do;
			income_group = 5;
			end;
	run;

proc sort data = dummy out = dummy;
	by income_group;
  	run;

proc means data = dummy noprint; 
	freq weight;	
	by income_group;
	var access ind debtor_all debt_interest_assessed debt income bankruptcy utilization_above_10 utilization_above_25 utilization_above_50 utilization_above_75 utilization_above_95 utilization_above_99 utilization_above_100 limit limit_debtor utilization ind_debt_y ind_limit_y;
	output out = output_all_hh_by_income (drop = _:) mean(access ind debtor_all debt_interest_assessed debt income bankruptcy utilization_above_10 utilization_above_25 utilization_above_50 utilization_above_75 utilization_above_95 utilization_above_99 utilization_above_100 limit limit_debtor utilization ind_debt_y ind_limit_y) = access interest_assessed_debtors all_debtors debt_interest_assessed debt income bankruptcy utilization_above_10 utilization_above_25 utilization_above_50 utilization_above_75 utilization_above_95 utilization_above_99 utilization_above_100 limit limit_debtor utilization ind_debt_y ind_limit_y;
	run;

proc export data = output_all_hh_by_income
	outfile="output_all_hh_by_income.csv"
	dbms=csv replace;
run;

/* save only card holders to save rate dispersion and back out their limits and rates by income*/
data only_cc_hh;
	set dummy;
	if ( access = 0 ) then delete;
	if ( access = 1 & ( interest > 90 | interest < 0 ) ) then delete;	 
run;

/* only CC HH */
proc means data = only_cc_hh noprint; 
	freq weight;	
	var interest;
	output out = output_cc_hh (drop = _:) std(interest) = interest;
	run;

proc export data = output_cc_hh
	outfile="output_cc_hh.csv"
	dbms=csv replace;
run;

proc sort data = only_cc_hh out = only_cc_hh;
	by income_group;
  	run;

proc means data = only_cc_hh noprint; 
	freq weight;	
	by income_group;
	var access ind debtor_all debt_interest_assessed debt income bankruptcy utilization_above_10 utilization_above_25 utilization_above_50 utilization_above_75 utilization_above_95 utilization_above_99 utilization_above_100 limit limit_debtor utilization ind_debt_y ind_limit_y interest;
	output out = output_cc_hh_by_income (drop = _:) mean(access ind debtor_all debt_interest_assessed debt income bankruptcy utilization_above_10 utilization_above_25 utilization_above_50 utilization_above_75 utilization_above_95 utilization_above_99 utilization_above_100 limit limit_debtor utilization ind_debt_y ind_limit_y interest) = access interest_assessed_debtors all_debtors debt_interest_assessed debt income bankruptcy utilization_above_10 utilization_above_25 utilization_above_50 utilization_above_75 utilization_above_95 utilization_above_99 utilization_above_100 limit limit_debtor utilization ind_debt_y ind_limit_y interest;
	run;

proc export data = output_cc_hh_by_income
	outfile="output_cc_hh_by_income.csv"
	dbms=csv replace;
run;

/* by age */
data dummy;
	set organized;
	if ( age < 20 ) then delete;	
	run;

proc sort data = dummy out = dummy;
	by age5;
  	run;

proc means data = dummy noprint; 
	freq weight;	
	by age5;
	var access ind debtor_all debt_interest_assessed debt income bankruptcy utilization_above_10 utilization_above_25 utilization_above_50 utilization_above_75 utilization_above_95 utilization_above_99 utilization_above_100 limit limit_debtor utilization ind_debt_y ind_limit_y;
	output out = output_all_hh_by_age (drop = _:) mean(access ind debtor_all debt_interest_assessed debt income bankruptcy utilization_above_10 utilization_above_25 utilization_above_50 utilization_above_75 utilization_above_95 utilization_above_99 utilization_above_100 limit limit_debtor utilization ind_debt_y ind_limit_y) = access interest_assessed_debtors all_debtors debt_interest_assessed debt income bankruptcy utilization_above_10 utilization_above_25 utilization_above_50 utilization_above_75 utilization_above_95 utilization_above_99 utilization_above_100 limit limit_debtor utilization ind_debt_y ind_limit_y;
	run;

proc export data = output_all_hh_by_age
	outfile="output_all_hh_by_age.csv"
	dbms=csv replace;
run;

/* save only card holders to back out their limits and rates by age*/
data only_cc_hh;
	set dummy;
	if ( access = 0 ) then delete;
	if ( access = 1 & ( interest > 90 | interest < 0 ) & year > 1994 ) then delete;	 
run;

proc sort data = only_cc_hh out = only_cc_hh;
	by age5;
  	run;

proc means data = only_cc_hh noprint; 
	freq weight;	
	by age5;
	var access ind debtor_all debt_interest_assessed debt income bankruptcy utilization_above_10 utilization_above_25 utilization_above_50 utilization_above_75 utilization_above_95 utilization_above_99 utilization_above_100 limit limit_debtor utilization ind_debt_y ind_limit_y interest;
	output out = output_cc_hh_by_age (drop = _:) mean(access ind debtor_all debt_interest_assessed debt income bankruptcy utilization_above_10 utilization_above_25 utilization_above_50 utilization_above_75 utilization_above_95 utilization_above_99 utilization_above_100 limit limit_debtor utilization ind_debt_y ind_limit_y interest) = access interest_assessed_debtors all_debtors debt_interest_assessed debt income bankruptcy utilization_above_10 utilization_above_25 utilization_above_50 utilization_above_75 utilization_above_95 utilization_above_99 utilization_above_100 limit limit_debtor utilization ind_debt_y ind_limit_y interest;
	run;

proc export data = output_cc_hh_by_age
	outfile="output_cc_hh_by_age.csv"
	dbms=csv replace;
run;
